package com.sq.partner.manager.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.dao.RuleConfigerDao;
import com.sq.partner.manager.mybatis.model.GatewayUser;
import com.sq.partner.manager.mybatis.model.RuleDefinition;
import com.sq.partner.manager.mybatis.model.RuleTemplateRale;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;

/**
 * 规则配置
 * 
 * SQ
 *
 */
@Repository
public class RuleConfigerDaoImpl extends BaseDaoImpl implements RuleConfigerDao {
	private static Logger logger = LogUtil.getLog(RuleConfigerDaoImpl.class);
	@Override
	public Page<Map<String, Object>> queryPlanPageByCriteria(Page<Map<String, Object>> page,
			GatewayUser user) throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("  select plan.plan_name, plan.plan_no,plan.partner_no, info.channel_name, dict.code_name,prod.risk_main_type,prod.PROD_TYPE from T_NLIFE_PLAN plan inner join t_nlife_prod prod ");
			sqlbuf.append("  on prod.prod_code = plan.prod_code  inner join t_gateway_channel_info info on plan.partner_no = info.channel_code ");
			sqlbuf.append("  left join t_gateway_dict dict  on dict.code_type = 'risk_main_type' and prod.risk_main_type = dict.code ");
			sqlbuf.append("  where ");
			//sqlbuf.append("   plan.agent_type='3' AND prod.Risk_Main_Type IS NOT NULL ");
			sqlbuf.append("   plan.agent_type='3' and info.trans_type = '1' and  prod.PROD_TYPE = '3006'  ");
			sqlbuf.append("   and info.dept_code in ( select t.dept_code  from t_core_dept t  connect by prior dept_code = snr_dept  start with dept_code in ( "+user.getDepartmentStr()+" ))");
			Map<String, Object> map=page.getMap();
			
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			if(map.get("partnerNo")!=null){
				 	sqlbuf.append(" and plan.partner_no= ?");
				    list.add((String) map.get("partnerNo"));
				    islike.add(false);
			}
			if(map.get("planCode")!=null){
				sqlbuf.append(" and plan.plan_no like ?");
				String planCode=(String) map.get("planCode");
				list.add(planCode);
				islike.add(true);
			}
			
			if(map.get("planName")!=null){
				sqlbuf.append(" and plan.plan_Name like ?");
				String planName=(String) map.get("planName");
				list.add(planName);
				islike.add(true);
			}
			String sql;
			sqlbuf.append(" order by plan.plan_no desc");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);

			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			
			//查出分页
			page.setTotal(total);
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}
	@Override
	public List<Map<String, Object>> queryTemplate(Map<String, Object> map)
			throws Exception {
		
		return this.queryList("selectTemplateMap", map);
	}
	@Override
	public String queryMaxOccup(Map<String, Object> map)throws Exception {
		Object query = this.query("queryMaxOccup", map);
		return query==null?null:query.toString();
	}
	@Override
	public String queryNextRuleId(String ruleStr) throws Exception {
		Object object = this.query("queryNextRuleId", ruleStr);//参数为方案号的前缀
		return object==null?null:object.toString();
	}
	@Override
	public Boolean saveRuleDefinition(RuleDefinition definition) throws Exception {
		return this.add("insertRuleDefinition", definition);
	}
	@Override
	public Boolean insertRuleTemplateRaleList(List<RuleTemplateRale> list)
			throws Exception {
		// TODO Auto-generated method stub
		return this.add("insertRuleTemplateRaleList", list);
	}
	@Override
	public Page<RuleDefinition> queryDefinitionPage(Page<RuleDefinition> page)
			throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		Map<String, Object> map=page.getMap();
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append(" select def.id,TO_CHAR(def.insert_time, 'yyyy-MM-dd HH24:mi:ss') insert_time,def.plan_no, def.partner_no, def.rule_name, info.channel_name from t_rule_Definition def left join t_gateway_channel_info info  on info.channel_code=def.partner_no where def.partner_no in (select info.channel_code");
			sqlbuf.append("  from t_gateway_channel_info info where info.dept_code in  (select t.dept_code  from t_core_dept t ");
			sqlbuf.append("  connect by prior dept_code = snr_dept  start with dept_code in ( "+map.get("deptStr")+" ))) ");
			
			
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			if(map.get("partnerNo")!=null){
				sqlbuf.append(" and partner_no like ?");
				String partnerNo=(String) map.get("partnerNo");
				list.add(partnerNo);
				islike.add(true);
			}
			
			if(map.get("ruleName")!=null){
				sqlbuf.append(" and rule_name like ?");
				String ruleName=(String) map.get("ruleName");
				list.add(ruleName);
				islike.add(true);
			}
			if(map.get("ruleName")!=null){
				sqlbuf.append(" and rule_name like ?");
				String ruleName=(String) map.get("ruleName");
				list.add(ruleName);
				islike.add(true);
			}
			if(map.get("planNo")!=null){
				sqlbuf.append(" and plan_no = ?");
				String planNo=(String) map.get("planNo");
				list.add(planNo);
				islike.add(false);
			}
			String sql;
			sqlbuf.append(" order by def.insert_time desc");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);

			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			
			//查出分页
			page.setTotal(total);
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}
	@Override
	public List<Map<String, Object>> queryEditTemplate(Map<String, Object> map)
			throws Exception {
		return this.queryList("editTemplateMap", map);
	}
	@Override
	public Map<String, Object> queryDefinitionById(String id) throws Exception {
		
		return (Map<String, Object>) this.query("queryDefinitionById", id);
	}
	@Override
	public Boolean editRuleDefinition(RuleDefinition definition)
			throws Exception {
		return this.update("editRuleDefinition", definition);
	}
	@Override
	public Boolean deleteTemplatByRuleId(String id)
			throws Exception {
		return this.delete("deleteTemplateMap", id);
	}
	@Override
	public Boolean deleteRuleByRuleId(String id) throws Exception {
		
		return this.delete("deleteRuleDefinition", id);
	}
}
